-- @owner: liu-tong-8848
-- @date: 2024-10-28
-- @testpoint: 分区表DDL回滚,分别使用全局分区索引扫,全表扫描数据成功

--step1:建表;expect:成功
drop table if exists t_partition_range_0174; 
create table t_partition_range_0174 (id int, name varchar2(100)) partition by range (id) ( partition p1 values less than (10), partition p2 values less than (100) ); 

--step2:创建索引;expect:成功
drop index if exists i_partition_range_0174; 
create unique index i_partition_range_0174 on t_partition_range_0174(id) global; 

--step3:向表插入数据;expect:成功
insert into t_partition_range_0174 values (1,'a'),(10,'b'); 

--step4:使用全局分区索引扫描;expect:成功
select /*+ indexscan(t_partition_range_0174 i_partition_range_0174 ) */ * from t_partition_range_0174 where id < 200;

--step5:开启事务,修改表数据后回滚;expect:成功
begin;
/
alter table t_partition_range_0174 drop partition p2 update global index; 
alter table t_partition_range_0174 add partition p2 end (100); 
select /*+ indexscan(t_partition_range_0174 i_partition_range_0174) */ * from t_partition_range_0174 where id < 200; 
insert into t_partition_range_0174 values (10,'bb'); 
select /*+ indexscan(t_partition_range_0174 i_partition_range_0174) */ * from t_partition_range_0174 where id < 200; 
rollback;

--step6:分别使用全局分区索引扫描和全表扫描;expect:成功
select /*+ indexscan(t_partition_range_0174 i_partition_range_0174) */ * from t_partition_range_0174 where id < 200; 
select * from t_partition_range_0174 where id < 200;

--step7:清理环境;expect:成功 
drop table if exists t_partition_range_0174 cascade;
